﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Web;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace WcfAtletIS
{
    public class AtletisService : IAtletisService
    {
        #region Private fields

        private string _sqlDetails;

        #endregion

        #region Get

        public DataTable GetTable(SyncParameters parameters, ref int error, ref string errorText)
        {
            error = 0;
            errorText = string.Empty;

            try
            {
                string sql = string.Empty;
                string connStr = ConfigurationManager.ConnectionStrings["AtletisContext"].ConnectionString;

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd;

                #region SWITCH

                switch (parameters.ActiveTable)
                {
                    #region Categories / Disciplines (3)

                    case Tables.Categories:
                        sql = "SELECT * FROM Categories ORDER BY Name";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.DisciplinesGroups:
                        sql = "SELECT * FROM DisciplinesGroups ORDER BY Name";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Disciplines:
                        sql = "SELECT * FROM Disciplines ORDER BY Name";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region Countries / Clubs / Competitors (4)

                    case Tables.Countries:
                        sql = "SELECT * FROM Countries";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ClubsGroups:
                        sql = "SELECT * FROM ClubsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Clubs:
                        sql = "SELECT * FROM Clubs";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Competitors:
                        sql = "SELECT * FROM Competitors";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region ChipTags (2)

                    case Tables.ChipTagsGroups:
                        sql = "SELECT * FROM ChipTagsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTags:
                        sql = "SELECT * FROM ChipTags ORDER BY ChipTagsGroupId, StartNumber";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTagsByGroupId:
                        sql = "SELECT * FROM ChipTags WHERE ChipTagsGroupId = @ID ORDER BY StartNumber";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region Competition tables (5)

                    case Tables.Competitions:
                        sql = "SELECT * FROM Competitions WHERE DateFrom BETWEEN @DateFrom AND @DateTo";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@DateFrom", SqlDbType.SmallDateTime);
                        cmd.Parameters["@DateFrom"].Value = parameters.DateFrom;
                        cmd.Parameters.Add("@DateTo", SqlDbType.SmallDateTime);
                        cmd.Parameters["@DateTo"].Value = parameters.DateTo;
                        break;

                    case Tables.CompetitionsById:
                        sql = "SELECT * FROM Competitions WHERE ID = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    case Tables.DisciplineListForCompetition:
                        sql = "SELECT * FROM DisciplineList WHERE CompetitionId = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    case Tables.Performances:
                        sql = "SELECT p.* FROM Performances p INNER JOIN DisciplineList dl ON dl.ID = p.DisciplineListId WHERE dl.CompetitionId = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    case Tables.CheckPoints:
                        sql = "SELECT * FROM CheckPoints WHERE DisciplineListId = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    case Tables.ExtTextsForDisciplineList:
                        sql = "SELECT et.* FROM ExtTexts et "; 
                        sql += "INNER JOIN DisciplineList dl on dl.ID = et.DisciplineListId ";
                        sql += "WHERE dl.CompetitionId = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    case Tables.ExtTextsForPerformance:
                        sql = "SELECT et.* FROM ExtTexts et ";
                        sql += "INNER JOIN Performances p ON p.ID = et.PerformanceId ";
                        sql += "INNER JOIN DisciplineList dl on dl.ID = p.DisciplineListId ";
                        sql += "WHERE dl.CompetitionId = @ID";
                        cmd = new SqlCommand(sql);
                        cmd.Parameters.Add("@ID", SqlDbType.Int);
                        cmd.Parameters["@ID"].Value = parameters.ID;
                        break;

                    #endregion

                    default:
                        throw new FaultException("WCF GetTable: Table not specified!");
                }

                #endregion

                cmd.Connection = conn;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                DataSet ds = new DataSet();
                da.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw new FaultException("WCF GetTable: " + ex.ToString());
            }
        }

        #endregion

        #region Set

        private void ResolveDuplicities(string tableName, DataTable dt)
        {
            try
            {
                DataSet ds;
                
                string sql = "SELECT 1 as [COL1] FROM [" + tableName + "] WHERE [WebId] = {0}";
                string connStr = ConfigurationManager.ConnectionStrings["AtletisContext"].ConnectionString;

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd;

                if (dt != null && dt.Rows.Count > 0)
                {
                    foreach (DataRow dr in dt.Rows)
                    {
                        // If Added, check if this WebId is not in WebDB
                        if (dr.RowState == DataRowState.Added)
                        {
                            ds = new DataSet();

                            string currentSql = string.Format(sql, dr["WebId"]);
                            cmd = new SqlCommand(currentSql, conn);

                            using (var adapter = new SqlDataAdapter(cmd))
                            {
                                adapter.Fill(ds);
                                if (ds != null && ds.Tables[0].Rows.Count > 0)
                                {
                                    dr.AcceptChanges();
                                    dr.SetModified();
                                }
                            }
                        }
                    }
                }
            }
            catch { }
        }

        private void OnRowUpdating(object sender, SqlRowUpdatingEventArgs e)
        {
            if (e != null && e.Command != null)
            {
                _sqlDetails = "SQL: " + e.Command.CommandText + Environment.NewLine;
                _sqlDetails += "Parametre:" + Environment.NewLine;

                if (e.Command.Parameters != null && e.Command.Parameters.Count > 0)
                {
                    foreach (SqlParameter par in e.Command.Parameters)
                    {
                        _sqlDetails += par.ParameterName + ": " + par.Value.ToString() + Environment.NewLine;
                    }
                }
            }
        }
        
        public DataTable SetTable(DataTable dt, SyncParameters parameters, ref int error, ref string errorText)
        {
            error = 0;
            errorText = string.Empty;

            _sqlDetails = string.Empty;

            try
            {
                string sql = string.Empty;
                string connStr = ConfigurationManager.ConnectionStrings["AtletisContext"].ConnectionString;

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd;

                #region SWITCH

                switch (parameters.ActiveTable)
                {
                    #region Categories / Disciplines (3)

                    case Tables.Categories:
                        sql = "SELECT * FROM Categories";
                        cmd = new SqlCommand(sql);
                        break;
                    
                    case Tables.DisciplinesGroups:
                        sql = "SELECT * FROM DisciplinesGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Disciplines:
                        sql = "SELECT * FROM Disciplines";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region Countries / Clubs / Competitors (4)

                    case Tables.Countries:
                        sql = "SELECT * FROM Countries";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ClubsGroups:
                        sql = "SELECT * FROM ClubsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Clubs:
                        sql = "SELECT * FROM Clubs";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Competitors:
                        sql = "SELECT * FROM Competitors";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region ChipTags (2)

                    case Tables.ChipTagsGroups:
                        sql = "SELECT * FROM ChipTagsGroups";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTags:
                        sql = "SELECT * FROM ChipTags";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ChipTagsByGroupId:
                        sql = "SELECT * FROM ChipTags";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    #region Competition tables (5)

                    case Tables.Competitions:
                    case Tables.CompetitionsById:
                        sql = "SELECT * FROM Competitions";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.DisciplineListForCompetition:
                        this.ResolveDuplicities("DisciplineList", dt);
                        sql = "SELECT * FROM DisciplineList";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.CheckPoints:
                        sql = "SELECT * FROM CheckPoints";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.Performances:
                        this.ResolveDuplicities("Performances", dt);
                        sql = "SELECT * FROM Performances";
                        cmd = new SqlCommand(sql);
                        break;

                    case Tables.ExtTextsForDisciplineList:
                    case Tables.ExtTextsForPerformance:
                        this.ResolveDuplicities("ExtTexts", dt);
                        sql = "SELECT * FROM ExtTexts";
                        cmd = new SqlCommand(sql);
                        break;

                    #endregion

                    default:
                        throw new FaultException("WCF SetTable: Table not specified!");
                }

                #endregion

                cmd.Connection = conn;

                using (var adapter = new SqlDataAdapter(cmd))
                {
                    using (var builder = new SqlCommandBuilder(adapter))
                    {
                        builder.ConflictOption = ConflictOption.OverwriteChanges;
                        builder.SetAllValues = true;

                        adapter.RowUpdating += new SqlRowUpdatingEventHandler(OnRowUpdating);

                        adapter.Update(dt);

                        return GetTable(parameters, ref error, ref errorText);
                    }
                }
            }
            catch (Exception ex)
            {
                throw new FaultException("WCF SetTable: " + ex.ToString() + Environment.NewLine + "SQL details: " + _sqlDetails);
            }
        }

        public int SetChipData(DataTable dt, ref int error, ref string errorText)
        {
            error = 0;
            errorText = string.Empty;

            _sqlDetails = string.Empty;

            int count = 0;

            try
            {
                string sql = string.Empty;
                sql += "IF NOT EXISTS (SELECT * FROM ChipData WHERE CompetitionId = @CompetitionId AND MeasurementId = @MeasurementId AND PcId = @PcId AND DataIndex = @DataIndex) BEGIN" + Environment.NewLine;
                sql += "  INSERT INTO ChipData(CompetitionId, MeasurementId, PcId, DataIndex, ChipTag, ChipTagsGroupId, LapCount, ChipDateTime)" + Environment.NewLine;
                sql += "  VALUES (@CompetitionId, @MeasurementId, @PcId, @DataIndex, @ChipTag, @ChipTagsGroupId, @LapCount, @ChipDateTime)" + Environment.NewLine;
                sql += "END";
                string connStr = ConfigurationManager.ConnectionStrings["AtletisContext"].ConnectionString;

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd;

                foreach (DataRow dr in dt.Rows)
                {
                    cmd = new SqlCommand(sql, conn);

                    cmd.Parameters.Add("@CompetitionId", SqlDbType.Int);
                    cmd.Parameters.Add("@MeasurementId", SqlDbType.Int);
                    cmd.Parameters.Add("@PcId", SqlDbType.VarChar, 10);
                    cmd.Parameters.Add("@DataIndex", SqlDbType.Int);
                    cmd.Parameters.Add("@ChipTag", SqlDbType.VarChar, 10);
                    cmd.Parameters.Add("@ChipTagsGroupId", SqlDbType.Int);
                    cmd.Parameters.Add("@LapCount", SqlDbType.Int);
                    cmd.Parameters.Add("@ChipDateTime", SqlDbType.DateTime);

                    cmd.Parameters["@CompetitionId"].Value = dr["CompetitionId"];
                    cmd.Parameters["@MeasurementId"].Value = dr["MeasurementId"];
                    cmd.Parameters["@PcId"].Value = dr["PcId"];
                    cmd.Parameters["@DataIndex"].Value = dr["DataIndex"];
                    cmd.Parameters["@ChipTag"].Value = dr["ChipTag"];
                    cmd.Parameters["@ChipTagsGroupId"].Value = dr["ChipTagsGroupId"];
                    cmd.Parameters["@LapCount"].Value = dr["LapCount"];
                    cmd.Parameters["@ChipDateTime"].Value = dr["ChipDateTime"];

                    // Execute
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    count += cmd.ExecuteNonQuery();
                    conn.Close();
                }
                
                return count;
            }
            catch (Exception ex)
            {
                throw new FaultException("WCF SetChipData: " + ex.Message + Environment.NewLine + _sqlDetails);
            }
        }

        public int SetPreChipData(DataTable dt, ref int error, ref string errorText)
        {
            error = 0;
            errorText = string.Empty;

            _sqlDetails = string.Empty;

            int count = 0;

            try
            {
                string sql = string.Empty;
                string connStr = ConfigurationManager.ConnectionStrings["AtletisContext"].ConnectionString;

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd;

                #region Delete PreChipData for competition

                if (dt != null && dt.Rows.Count > 0)
                {
                    sql = "DELETE FROM PreChipData WHERE CompetitionId = @CompetitionId";
                    
                    cmd = new SqlCommand(sql, conn);
                    cmd.Parameters.AddWithValue("@CompetitionId", dt.Rows[0]["CompetitionId"]);

                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    cmd.ExecuteNonQuery();
                    conn.Close();
                }

                #endregion

                #region New PreChipData

                sql = "INSERT INTO PreChipData(CompetitionId, MeasurementId, PcId, StartNumber, PreChipData) ";
                sql += "VALUES (@CompetitionId, @MeasurementId, @PcId, @StartNumber, @PreChipData)";

                foreach (DataRow dr in dt.Rows)
                {
                    cmd = new SqlCommand(sql, conn);

                    cmd.Parameters.AddWithValue("@CompetitionId", dr["CompetitionId"]);
                    cmd.Parameters.AddWithValue("@MeasurementId", dr["MeasurementId"]);
                    cmd.Parameters.AddWithValue("@PcId", dr["PcId"]);
                    cmd.Parameters.AddWithValue("@StartNumber", dr["StartNumber"]);
                    cmd.Parameters.AddWithValue("@PreChipData", dr["PreChipData"]);

                    if (conn.State != ConnectionState.Open)
                        conn.Open();

                    count += cmd.ExecuteNonQuery();
                    conn.Close();
                }

                #endregion

                return count;
            }
            catch (Exception ex)
            {
                throw new FaultException("WCF SetPreChipData: " + ex.Message + Environment.NewLine + _sqlDetails);
            }
        }

        #endregion

    }
}
